﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

using Microsoft.ApplicationBlocks.Data;

using YouHP.IDAL;
using YouHP.Model;
using YouHP.Utility;
using System.Data.SqlClient;

namespace YouHP.SqlServerDAL
{
    public class PatientIndicatorDAL : IPatientIndicator
    {
        #region private functions
        // 添加单条的实验室指标历史记录,采用事务方式
        private int AddIndicatorHistory(SqlTransaction ts, IndicatorHistoryInfo info)
        {
            //判断当前对应的记录是否已经存在，如存在，则执行更新操作
            int historyId = GetIndicatorHistory(ts, info.PRId, info.IndicatorId, info.RecordDate);
            if (historyId > 0)
            {
                //执行update操作，覆盖原来的数据
                info.Id = historyId;
                UpdateIndicatorHistory(ts, info);
                return historyId;
            }

            SqlParameter[] outParms = new SqlParameter[]  {
                new SqlParameter("@PRId", SqlDbType.Int),
                new SqlParameter("@IndicatorId", SqlDbType.Int),
                new SqlParameter("@SurveyId", SqlDbType.Int),
                new SqlParameter("@DataValue", SqlDbType.Float),
                new SqlParameter("@RecordDate", SqlDbType.DateTime),
                new SqlParameter("@UpdateDate", SqlDbType.DateTime),
                new SqlParameter("@Remark", SqlDbType.NVarChar, 2000),
                new SqlParameter("@Id", SqlDbType.Int)
            };
            outParms[0].Value = info.PRId;
            outParms[1].Value = info.IndicatorId;
            outParms[2].Value = info.SurveyId;
            outParms[3].Value = info.DataValue;
            outParms[4].Value = info.RecordDate;
            outParms[5].Value = info.UpdateDate;
            outParms[6].Value = info.Remark;
            outParms[7].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteNonQuery(ts, CommandType.StoredProcedure, Common.PatientIndicatorSP_AddIndicatorHistory, outParms);

            return Convert.ToInt32(outParms[7].Value.ToString());
        }

        // 修改单条的症状历史记录,采用事务方式
        private void UpdateIndicatorHistory(SqlTransaction ts, IndicatorHistoryInfo info)
        {
            SqlHelper.ExecuteNonQuery(ts,
                Common.PatientIndicatorSP_UpdateIndicatorHistory,
                info.Id,
                info.PRId,
                info.IndicatorId,
                info.SurveyId,
                info.DataValue,
                info.RecordDate,
                info.UpdateDate,
                info.Remark
                );

            return;
        }

        // 删除当前记录表中的纪录,采用事务方式
        private bool DeleteCurrentIndicator(SqlTransaction ts, int prId, int IndicatorId)
        {
            string condStr = string.Format(@" [PatientCurrentIndicator].[PRId]={0} AND [PatientCurrentIndicator].[IndicatorId]={1}",
                prId, IndicatorId);

            return SqlHelper.ExecuteNonQuery(ts,
                Common.PatientIndicatorSP_DelCurrentIndicator,
                condStr.ToString()) > 0;
        }

        // 添加当前的症状记录, 采用事务方式
        private void AddCurrentIndicator(SqlTransaction ts, IndicatorHistoryInfo info)
        {
            SqlHelper.ExecuteNonQuery(ts,
                Common.PatientIndicatorSP_AddCurrentIndicator,
                info.PRId,
                info.IndicatorId,
                info.DataValue,
                info.IndicatorHistoryId
                );

            return;
        }

        // 修改当前的症状记录, 采用事务方式
        private void UpdateCurrentIndicator(SqlTransaction ts, IndicatorHistoryInfo info)
        {
            SqlHelper.ExecuteNonQuery(ts,
                Common.PatientIndicatorSP_UpdateCurrentIndicator,
                info.PRId,
                info.IndicatorId,
                info.DataValue,
                info.IndicatorHistoryId
                );

            return;
        }

        // 根据历史Id删除症状历史记录信息,采用事务方式
        private bool DeleteIndicatorHistory(SqlTransaction ts, int prId, int historyId)
        {
            string condStr = string.Format(@" [PatientIndicatorHistory].[PRId]={0} AND [PatientIndicatorHistory].[Id]={1}",
                prId, historyId);

            return SqlHelper.ExecuteNonQuery(ts,
                Common.PatientIndicatorSP_DelIndicatorHistory,
                condStr.ToString()) > 0;
        }

        // 检查当前对应的记录是否已经存在
        private int GetIndicatorHistory(SqlTransaction ts, int prId, int IndicatorId, DateTime recordDate)
        {
            string condStr = string.Format(@" [PatientIndicatorHistory].[PRId]={0} AND [PatientIndicatorHistory].[IndicatorId]={1} AND "
                + " [PatientIndicatorHistory].[RecordDate]='{2}' ", prId, IndicatorId, recordDate);

            IDataReader reader = SqlHelper.ExecuteReader(ts,
                Common.PatientIndicatorSP_GetIndicatorHistory,
                condStr) as IDataReader;
            IndicatorHistoryInfo shInfo = CBO.FillObject<IndicatorHistoryInfo>(reader);

            return shInfo.Id;
        }

        // 取得记录表中最早的一条
        private IndicatorHistoryInfo GetLasttestIndicatorHistory(SqlTransaction ts, int prId, int IndicatorId)
        {
            string condStr = string.Format(@" [PatientIndicatorHistory].[PRId]={0} AND [PatientIndicatorHistory].[IndicatorId]={1} AND "
                + " [PatientIndicatorHistory].[RecordDate]=( SELECT MAX(RecordDate) from PatientIndicatorHistory "
                + " WHERE [PatientIndicatorHistory].[PRId] = {0} AND [PatientIndicatorHistory].[IndicatorId]={1} ) ", prId, IndicatorId);

            IDataReader reader = SqlHelper.ExecuteReader(ts,
                Common.PatientIndicatorSP_GetIndicatorHistory,
                condStr) as IDataReader;
            return CBO.FillObject<IndicatorHistoryInfo>(reader);
        }

        // 根据纪录时间来判断当前记录是否为历史纪录中最早的纪录
        private bool CheckLasttestIndicatorHistory(SqlTransaction ts, int prId, int IndicatorId, DateTime recordDate)
        {
            bool retVal = true;

            string condStr = string.Format(@" [PatientIndicatorHistory].[PRId]={0} AND [PatientIndicatorHistory].[IndicatorId]={1} AND "
                + " [PatientIndicatorHistory].[RecordDate] > '{2}' ", prId, IndicatorId, recordDate);

            IDataReader dr = SqlHelper.ExecuteReader(ts,
                Common.PatientIndicatorSP_GetIndicatorHistoryCount,
                condStr);

            if (dr != null && dr.Read())
            {
                if (dr.GetInt32(0) > 0)
                {
                    retVal = false; //存在则说明,该记录不是最新的记录
                }
                dr.Close();
            }

            return retVal;
        }

        // 判断当前纪录表中是否已存在纪录
        private bool CheckExsitCurrentIndicatorHistory(SqlTransaction ts, int prId, int IndicatorId)
        {
            bool retVal = false;

            string condStr = string.Format(@" [PatientCurrentIndicator].[PRId]={0} AND [PatientCurrentIndicator].[IndicatorId]={1} ",
                prId, IndicatorId);

            IDataReader dr = SqlHelper.ExecuteReader(ts,
                Common.PatientIndicatorSP_GetCurrentIndicatorCount,
                condStr);

            if (dr != null && dr.Read())
            {
                if (dr.GetInt32(0) > 0)
                {
                    retVal = true;
                }
                dr.Close();
            }

            return retVal;
        }

        // 在当前记录表查找某条历史纪录
        private IndicatorHistoryInfo GetCurrentIndicator(SqlTransaction ts, int prId, int historyId)
        {
            string condStr = string.Format(@" [PatientCurrentIndicator].[PRId]={0} AND [PatientCurrentIndicator].[IndicatorHistoryId]={1}",
                prId, historyId);

            IDataReader reader = SqlHelper.ExecuteReader(ts,
                Common.PatientIndicatorSP_GetCurrentIndicator,
                condStr) as IDataReader;
            return CBO.FillObject<IndicatorHistoryInfo>(reader);
        }

        // 判断当前纪录表中是否已存在纪录
        private bool CheckExsitCurrentIndicatorHistoryByHistoryId(SqlTransaction ts, int prId, int historyId)
        {
            bool retVal = false;

            string condStr = string.Format(@" [PatientCurrentIndicator].[PRId]={0} AND [PatientCurrentIndicator].[IndicatorHistoryId]={1} ",
                prId, historyId);

            IDataReader dr = SqlHelper.ExecuteReader(ts,
                Common.PatientIndicatorSP_GetCurrentIndicatorCount,
                condStr);

            if (dr != null && dr.Read())
            {
                if (dr.GetInt32(0) > 0)
                {
                    retVal = true;
                }
                dr.Close();
            }

            return retVal;
        }

        // 获得批量的症状历史记录,采用事务的方式
        private List<IndicatorHistoryInfo> GetIndicatorSurvey(SqlTransaction ts, int prId, int surveyId)
        {
            string condStr = string.Format(@" [PatientIndicatorHistory].[PRId]={0} AND [PatientIndicatorHistory].[SurveyId]={1} ",
                prId, surveyId);

            IDataReader dr = SqlHelper.ExecuteReader(ts,
                Common.PatientIndicatorSP_GetSurveyHistoryIndicatorList,
                condStr);

            return CBO.FillCollection<IndicatorHistoryInfo>(dr);
        }
        #endregion

        //判断历史记录表中是否已存在病人指定时间对应的记录
        public bool CheckIndicatorHistory(int prId, int indicatorId, DateTime recordDate)
        {
            bool retVal = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                int shId = GetIndicatorHistory(ts, prId, indicatorId, recordDate);
                if (shId > 0)
                {
                    retVal = true;
                }

                ts.Commit();
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 添加单条的实验室指标历史记录
        public bool AddIndicatorHistory(IndicatorHistoryInfo info)
        {
            bool retVal = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                info.IndicatorHistoryId = AddIndicatorHistory(ts, info);

                //判断当前记录是否为历史纪录中最早的纪录，如是则需要更新当前纪录表，否则不用处理
                if (CheckLasttestIndicatorHistory(ts, info.PRId, info.IndicatorId, info.RecordDate))
                {
                    //判断当前表中是否存在记录，存在则需要更新当前的记录值，否则执行添加
                    if (CheckExsitCurrentIndicatorHistory(ts, info.PRId, info.IndicatorId))
                    {
                        //执行修改
                        UpdateCurrentIndicator(ts, info);
                    }
                    else
                    {
                        //执行添加
                        AddCurrentIndicator(ts, info);
                    }
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 取得病人的某种实验室指标的当前值
        public IndicatorHistoryInfo GetCurrentIndicator(int prId, int indicatorId)
        {
            string condStr = string.Format(@" [PatientCurrentIndicator].[PRId]={0} AND [PatientCurrentIndicator].[IndicatorId]={1} ",
                prId, indicatorId);

            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientIndicatorSP_GetCurrentIndicator,
                condStr) as IDataReader;
            return CBO.FillObject<IndicatorHistoryInfo>(reader);
        }

        // 根据历史Id获得实验室指标历史记录信息
        public IndicatorHistoryInfo GetIndicatorHistory(int prId, int historyId)
        {
            string condStr = string.Format(@" [PatientIndicatorHistory].[PRId]={0} AND [PatientIndicatorHistory].[Id]={1}",
                prId, historyId);

            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientIndicatorSP_GetIndicatorHistory,
                condStr) as IDataReader;
            return CBO.FillObject<IndicatorHistoryInfo>(reader);
        }

        // 修改单条的实验室指标历史记录
        public bool UpdateIndicatorHistory(IndicatorHistoryInfo info)
        {
            bool retVal = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                UpdateIndicatorHistory(ts, info);
                info.IndicatorHistoryId = info.Id;    //为操作当前记录准备数据

                //判断当前记录是否为历史纪录中最早的纪录，如是则需要更新当前纪录表，否则不用处理
                if (CheckLasttestIndicatorHistory(ts, info.PRId, info.IndicatorId, info.RecordDate))
                {
                    //判断当前表中是否存在记录，存在则需要更新当前的记录值，否则执行添加
                    if (CheckExsitCurrentIndicatorHistory(ts, info.PRId, info.IndicatorId))
                    {
                        //执行修改
                        UpdateCurrentIndicator(ts, info);
                    }
                    else
                    {
                        //执行添加
                        AddCurrentIndicator(ts, info);
                    }
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 根据历史Id删除实验室指标历史记录信息
        public bool DeleteIndicatorHistory(int prId, int historyId)
        {
            bool retVal = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                //在当前记录表查找该历史纪录(最好是先取,再删除)
                IndicatorHistoryInfo historyInfo = GetCurrentIndicator(ts, prId, historyId);
                //从历史纪录表中删除该纪录
                DeleteIndicatorHistory(ts, prId, historyId);

                if (historyInfo.IndicatorId > 0)   //需要对当前记录进行操作
                {
                    //从历史纪录中再选出一条最新的记录
                    IndicatorHistoryInfo lasttestInfo = GetLasttestIndicatorHistory(ts, prId, historyInfo.IndicatorId);
                    if (lasttestInfo.Id <= 0)   //如果找不到,则将当前记录表中的内容删除
                    {
                        DeleteCurrentIndicator(ts, prId, historyInfo.IndicatorId);
                    }
                    else  //如果找到,则修改当前记录表中的数据
                    {
                        lasttestInfo.IndicatorHistoryId = lasttestInfo.Id;
                        UpdateCurrentIndicator(ts, lasttestInfo);
                    }
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 批量的实验室指标历史记录提交
        public bool AddIndicatorSurvey(List<IndicatorHistoryInfo> historyList)
        {
            bool retVal = false;
            int surveyId = -1;

            if (historyList.Count <= 0)
                return false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                //添加survey表
                PatientSurveyInfo psInfo = new PatientSurveyInfo();
                psInfo.PRId = historyList[0].PRId;
                psInfo.RecordDate = historyList[0].RecordDate;
                psInfo.UpdateDate = historyList[0].UpdateDate;
                psInfo.SurveyType = Constants.IndicatorSurvey;

                surveyId = Common.AddPatientSurvey(ts, psInfo);
                if (surveyId > 0)
                {
                    //批量添加症状历史记录表
                    for (int i = 0; i < historyList.Count; i++)
                    {
                        IndicatorHistoryInfo info = historyList[i];
                        info.SurveyId = surveyId;
                        info.IndicatorHistoryId = AddIndicatorHistory(ts, info);

                        //判断当前记录是否为历史纪录中最早的纪录，如是则需要更新当前纪录表，否则不用处理
                        if (CheckLasttestIndicatorHistory(ts, info.PRId, info.IndicatorId, info.RecordDate))
                        {
                            //判断当前表中是否存在记录，存在则需要更新当前的记录值，否则执行添加
                            if (CheckExsitCurrentIndicatorHistory(ts, info.PRId, info.IndicatorId))
                            {
                                //执行修改
                                UpdateCurrentIndicator(ts, info);
                            }
                            else
                            {
                                //执行添加
                                AddCurrentIndicator(ts, info);
                            }
                        }
                    }

                    ts.Commit();
                    retVal = true;
                }
                else
                {
                    ts.Rollback();
                }
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 获得批量的症状历史记录
        public List<IndicatorHistoryInfo> GetIndicatorSurvey(int prId, int surveyId)
        {
            string condStr = string.Format(@" [PatientIndicatorHistory].[PRId]={0} AND [PatientIndicatorHistory].[SurveyId]={1} ",
                prId, surveyId);

            IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientIndicatorSP_GetSurveyHistoryIndicatorList,
                condStr);

            return CBO.FillCollection<IndicatorHistoryInfo>(dr);
        }

        // 批量的实验室指标历史记录修改
        public bool UpdateIndicatorSurvey(int surveyId, List<IndicatorHistoryInfo> historyList)
        {
            bool retVal = false;
            if (historyList.Count <= 0)
                return false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                //更新survey表
                PatientSurveyInfo psInfo = new PatientSurveyInfo();
                psInfo.Id = surveyId;
                psInfo.PRId = historyList[0].PRId;
                psInfo.RecordDate = historyList[0].RecordDate;
                psInfo.UpdateDate = historyList[0].UpdateDate;

                if (Common.UpdatePatientSurvey(ts, psInfo))
                {
                    //批量更新症状历史记录表
                    for (int i = 0; i < historyList.Count; i++)
                    {
                        IndicatorHistoryInfo info = historyList[i];
                        info.SurveyId = surveyId;
                        UpdateIndicatorHistory(ts, info);
                        info.IndicatorHistoryId = info.Id;    //为操作当前记录准备数据

                        //判断当前记录是否为历史纪录中最早的纪录，如是则需要更新当前纪录表，否则不用处理
                        if (CheckLasttestIndicatorHistory(ts, info.PRId, info.IndicatorId, info.RecordDate))
                        {
                            //判断当前表中是否存在记录，存在则需要更新当前的记录值，否则执行添加
                            if (CheckExsitCurrentIndicatorHistory(ts, info.PRId, info.IndicatorId))
                            {
                                //执行修改
                                UpdateCurrentIndicator(ts, info);
                            }
                            else
                            {
                                //执行添加
                                AddCurrentIndicator(ts, info);
                            }
                        }

                        //如果当前记录的记录时间被修改,调整到非最新的日期,则需要重新更新当前记录表
                    }

                    ts.Commit();
                    retVal = true;
                }
                else
                {
                    ts.Rollback();
                }
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 根据SurveyId删除实验室指标历史记录信息
        public bool DeleteIndicatorSurvey(int prId, int surveyId)
        {
            bool retVal = false;
            int historyId = -1;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                // 从历史纪录中中检索出surveyId对应的所有记录信息
                List<IndicatorHistoryInfo> shList = GetIndicatorSurvey(ts, prId, surveyId);

                // 遍历每条记录判断是否出现在当前表中,
                foreach (IndicatorHistoryInfo info in shList)
                {
                    historyId = info.Id;
                    //在当前记录表查找该历史纪录(最好是先取,再删除)
                    IndicatorHistoryInfo curInfo = GetCurrentIndicator(ts, prId, historyId);
                    //从历史纪录表中删除该纪录
                    DeleteIndicatorHistory(ts, prId, historyId);

                    if (curInfo.IndicatorId > 0)   //需要对当前记录进行操作
                    {
                        //从历史纪录中再选出一条最新的记录
                        IndicatorHistoryInfo lasttestInfo = GetLasttestIndicatorHistory(ts, prId, curInfo.IndicatorId);
                        if (lasttestInfo.Id <= 0)   //如果找不到,则将当前记录表中的内容删除
                        {
                            DeleteCurrentIndicator(ts, prId, curInfo.IndicatorId);
                        }
                        else  //如果找到,则修改当前记录表中的数据
                        {
                            lasttestInfo.IndicatorHistoryId = lasttestInfo.Id;
                            UpdateCurrentIndicator(ts, lasttestInfo);
                        }
                    }

                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 取得病人的当前实验室指标列表
        public List<IndicatorHistoryInfo> GetCurrentIndicatorList(int prId)
        {
            string condStr = string.Format(@" [PatientCurrentIndicator].[PRId]={0} ", prId);

            IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientIndicatorSP_GetCurrentIndicatorList,
                condStr);

            return CBO.FillCollection<IndicatorHistoryInfo>(dr);
        }

        // 取得病人针对某种实验室指标的实验室指标记录历史
        public List<IndicatorHistoryInfo> GetIndicatorHistoryList(int prId, int indicatorId, int pageSize, int pageIndex, out int count)
        {
            string condStr = string.Format(@" [PatientIndicatorHistory].[PRId]={0} "+"AND [PatientIndicatorHistory].[IndicatorId]={1} ", prId, indicatorId);
            if (0 == indicatorId)
            {
                condStr = string.Format(@" [PatientIndicatorHistory].[PRId]={0} ", prId);
            }
            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
                Common.PatientIndicatorSP_GetIndicatorHistoryList,
                pageSize, pageIndex, condStr, " [PatientIndicatorHistory].[RecordDate] desc ");

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }
            dr = dset.Tables[1].CreateDataReader();

            return CBO.FillCollection<IndicatorHistoryInfo>(dr);
        }
    }
}
